drop table FLIGHT             cascade constraints;
drop table PLANE              cascade constraints;
drop table PRICE              cascade constraints;
drop table CUSTOMER           cascade constraints;
drop table RESERVATION        cascade constraints;
drop table RESERVATION_DETAIL cascade constraints;
drop table OUR_SYS_TIME           cascade constraints;


create table PLANE(
	plane_type     char(4),
	manufacture    varchar2(10),
	plane_capacity int,
	last_service   date,
	year           int,	
	constraint plane_pk primary key (plane_type) 
);
insert into PLANE values( 'B737', 'Boeing', 125, to_date('09-SEP-2009', 'DD-MON-YYYY'), 1996);
insert into PLANE values( 'A320', 'Airbus', 155, to_date('01-OCT-2011', 'DD-MON-YYYY'), 2001);
insert into PLANE values( 'E145', 'Embraer', 50, to_date('15-JUN-2010', 'DD-MON-YYYY'), 2008);

create table FLIGHT(
	flight_number   varchar2(3),
	plane_type      char(4),
	departure_city  varchar2(3),
	arrival_city    varchar2(3),
	departure_time  varchar2(4),
	arrival_time    varchar2(4),
	weekly_schedule varchar2(7), -- White space does matter.	
	constraint flight_pk primary key (flight_number),
	constraint plane_type_fk foreign key (plane_type) references Plane(plane_type)
);

insert into FLIGHT values('153', 'A320', 'PIT', 'JFK', '1000', '1120', 'SMTWTFS');
insert into FLIGHT values('154', 'B737', 'JFK', 'DCA', '1230', '1320', 'S-TW-FS');
insert into FLIGHT values('552', 'E145', 'PIT', 'DCA', '1100', '1150', 'SM-WT-S');


create table PRICE(
	departure_city varchar2(3),
	arrival_city   varchar2(3),
	high_price     int,
	low_price      int,	
	constraint price_pk primary key (Departure_city, arrival_city)
);
insert into PRICE values('PIT', 'JFK', 250, 120);
insert into PRICE values('JFK', 'PIT', 250, 120);
insert into PRICE values('JFK', 'DCA', 220, 100);
insert into PRICE values('DCA', 'JFK', 210, 90);
insert into PRICE values('PIT', 'DCA', 200, 150);
insert into PRICE values('DCA', 'PIT', 200, 150);


create table CUSTOMER(
	cid                varchar2(9),
	salutation         varchar2(3),
	first_name         varchar2(30),
	last_name          varchar2(30),
	credit_card_num    varchar2(16),
	credit_card_expire date,
	street             varchar2(30),
	city               varchar2(30),
	state              varchar2(2),
	phone              varchar2(10),
	email              varchar2(30),	
	constraint customer_pk primary key (cid)
);


create table RESERVATION(
	reservation_number varchar2(5),
	cid                varchar2(9),
	cost               int,
	reservation_date   date,
	ticketed           varchar2(1),	
	constraint reservation_pk primary key (reservation_number),
	constraint cid_fk foreign key (cid) references Customer(cid)
);


create table RESERVATION_DETAIL(
	reservation_number  varchar2(5),
	flight_number       varchar2(3),
	flight_date         date,
	leg                 int,	
	constraint reservation_detail_pk  primary key (reservation_number, leg),
	constraint reservation_detail_fk1 foreign key (reservation_number) references Reservation(reservation_number),
	constraint reservation_detail_fk2 foreign key (flight_number)      references Flight(flight_number)
);

create table OUR_SYS_TIME(
	c_date date,
	constraint date_pk primary key(c_date)
);

-- End Tables

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-- begin views

drop materialized view mv_manifest;
create materialized view mv_manifest(FLIGHT_NUM, FLIGHT_DATE, RESERVATION_NUM, CID, SALUTATION, FIRST_NAME, LAST_NAME)
build immediate refresh on demand
as select flight_number, flight_date, reservation_number, cid, salutation, first_name, last_name
from (Reservation_detail natural join Reservation) natural join Customer;

-- end views
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-- begin cid auto_incrementer
/* This following two guys simply auto-insert a cid for the customer! Enjoy.*/

drop sequence seq_cid_incrementer;
create sequence seq_cid_incrementer
start with 1
increment by 1
cache 10
nocycle;

create or replace trigger cid_auto_insert
before insert on customer
for each row
begin
	if :new.cid is null then
		:new.cid := to_char(seq_cid_incrementer.nextval);
		dbms_output.put_line('Null cid detected. Test values must be > 100000.' ||
							' A null cid in this context means that we want to auto_increment' ||
							' the cid based on seq_cid_incrementer.');
	else
		if :new.cid < '100000' then
			-- this is for testing purposes -- should not affect project in any way
			:new.cid := to_char(seq_cid_incrementer.nextval);
			dbms_output.put_line('cid_auto_insert auto_inserted (no test value detected: ' ||
								:new.cid ||'. Test value should be > 100000');
		else
			-- do nothing. We just update
			dbms_output.put_line('cid_auto_insert did not auto_insert (test value detected: '|| 
								:new.cid || ')');
		end if;
	end if;
end cid_auto_insert;
/

-- end cod auto_incrementer
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-- garbage below. Possibly some useful stuff.
/*
select Flight.flight_no into temp_flight_number
from Flight, reservation_detail 
where (:new.departure_city = Flight.departure_city)	and 
	(:new.arrive_city = Flight.arrival_city)
group by Flight.flight_number;
-- 
create or replace procedure

create or replace function sum_cost_of_legs(reservation_number in varchar2) return int as total_cost int;
begin
	select leg into total_cost
	from reservation_detail
	where leg = leg;
end;
/

-- get the current date
(SELECT SYSDATE FROM DUAL + 1) - (SELECT SYSDATE FROM DUAL);

-- This is nice to have output.
SET SERVEROUTPUT ON
BEGIN
   dbms_output.put_line(to_date('02-DEC-2012 10:59:59','DD-MON-YYYY HH24:MI:SS'));
END;
/

begin
if 1 = 1 then
dbms_output.put_line('success');
else
dbms_output.put_line('failure');
end if;
end;
/



begin
if (to_date('02-DEC-2012 10:59:59','DD-MON-YYYY HH24:MI:SS')) = (to_date('02-DEC-2012 10:59:59','DD-MON-YYYY HH24:MI:SS')) then
dbms_output.put_line('success');
else
dbms_output.put_line('failure');
end if;
end;
/
*/
